﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL1;
using dto2; //cần Add reference
using System.Data;
using System.Data.OleDb;

namespace BLL
{
    class SinhvienDAL
    {
           /* Có thể lấy tất cả dữ liệu của một bảng như sau
    public DataTable GetAll()
    {
        DataProvider dataprovider = new DataProvider();
        return dataprovider.ExecuteSelectQuery("Select * from KHOA", null);
    }
    */
            public List<SINHVIEN> GetAll()
            {
                SINHVIEN sv;
                List<SINHVIEN> list = new List<SINHVIEN>();
                DataProvider dataprovider = new DataProvider();
                DataTable datatable = new DataTable();
                datatable = dataprovider.ExecuteSelectQuery("Select * from SINHVIEN", null);
                for (int i = 0; i < datatable.Rows.Count; i++)
                {
                    sv = new SINHVIEN();
                    sv.MaSV = datatable.Rows[i]["MaSV"].ToString();
                    sv.Holot = datatable.Rows[i]["Holot"].ToString();
                    sv.Ten = datatable.Rows[i]["Ten"].ToString();

                    list.Add(sv);
                }
                return list;
            }

            public SINHVIEN GetSINHVIEN(byte maSV)
            {
                DataProvider dataprovider = new DataProvider();
                OleDbParameter[] parameters = new OleDbParameter[1];
                parameters[0] = new OleDbParameter("@maSV", OleDbType.TinyInt);
                parameters[0].Value = maSV;
                DataTable datatable = new DataTable();
                datatable = dataprovider.ExecuteSelectQuery("Select * from KHOA where MaSV = @maSV", parameters);
                SINHVIEN sv = new SINHVIEN();
                if (datatable.Rows.Count != 0)
                {
                    sv.MaSV = datatable.Rows[0]["MaSV"].ToString();
                    sv.Holot = datatable.Rows[0]["Holot"].ToString();
                    sv.Ten = datatable.Rows[0]["Ten"].ToString();
                    return sv;
                }
                else
                    return null;
            }

            public bool UpdateSINHVIEN(byte maSV, string holot, string ten, byte masvcu)
            {
                DataProvider dataprovider = new DataProvider();
                OleDbParameter[] parameters = new OleDbParameter[3];
                parameters[0] = new OleDbParameter("@maSV", OleDbType.TinyInt);
                parameters[0].Value = maSV;
                parameters[1] = new OleDbParameter("@holot", OleDbType.Char);
                parameters[1].Value = holot;
                parameters[2] = new OleDbParameter("@ten", OleDbType.Char);
                parameters[2].Value = ten;
                parameters[3] = new OleDbParameter("@makhoacu", OleDbType.TinyInt);
                parameters[3].Value = masvcu;
                return dataprovider.ExecuteNonSelectQuery("update SINHVIEN set MASV=@maSV, Holot=@holot, where MaSV = @masvcu", parameters);
            }
        }
    }

